import pandas as pd
from ListUtils import anti_join

"""
股票基础数据
"""

def query_industry(engine_):
    ndf = pd.read_csv("../../source/Table.txt", encoding='gbk', delim_whitespace=True)
    ndf.replace('\t', '', inplace=True)
    ndf['代码'] = ndf['代码'].str[2:] + '.' + ndf['代码'].str[:2]
    ndf.columns = ['ts_code', 'name', 'l2_name', 'l1_name']
    ndf = ndf[['ts_code', 'l2_name']]
    # print(len(ndf))
    sql = 'select s.ts_code "l2_code", s.industry_name "l2_name", s.parent_code "l1_code", l1.industry_name "l1_name" from index_classify s JOIN index_classify l1 ON s.parent_code = l1.ts_code  WHERE s.level =  "L2" or s.level = "L12"'
    industry_list = pd.read_sql(sql, engine_)

    # print(industry_list)
    ndf = pd.merge(ndf, industry_list, how='left', on='l2_name')

    # print(ndf)

    return ndf



def insert_history_data_stock(engine, pro):
    """
    保存历史数据，通过对比添加数据
    """
    # ts数据
    df_stock = pro.stock_basic(exchange='', list_status='L')
    industry_list = query_industry(engine)
    if len(df_stock) > 0 and len(industry_list) > 0:
        # industry_list.rename(columns={'industry_name': 'industry'}, errors='ignore', inplace=True)
        df_stock = pd.merge(df_stock, industry_list, how='left', on=['ts_code'])
        # print(new_list)
        # print(new_list.loc[0])
        # 数据库数据
        sql = 'select s.ts_code from stock s'
        target_list = pd.read_sql(sql, engine)
        if len(target_list) == 0:
            # 数据库内未找到数据则全部添加
            df_stock.to_sql('stock', con=engine, index=False, index_label='trade_code', if_exists='append')
            print('数据库内未找到数据则全部添加：(%s)' % df_stock)
        else:
            # 数据库内有历史数据则增量添加
            df_stock = anti_join(df_stock, target_list, 'ts_code')
            # 采用双for循环性能太差，采用dataFrame合并提高效率
            # for i in range(0, len(df_stock_company)):
            #     # 查询并插入
            #     that_ = df_stock_company.loc[i].ts_code
            #
            #     for j in range(0, len(target_list)):
            #
            #         # logging.debug('下方if判断：' + trade_cal_days.loc[j].cal_date == that_day and trade_cal_days.loc[j].is_open == '1')
            #         if not target_list.loc[j].exists and target_list.loc[j].ts_code == that_:
            #             print(j)
            #             # print(source_list.loc[i,['exists']])
            #             df_stock_company.loc[i,['exists']] = True
            #             target_list.loc[j, ['exists']] = True
            #             break

            if len(df_stock) > 0:
                print('增量：(%s)' % df_stock)
                # print(source_list)
                # df_stock_company.drop(labels='exists',axis=1,inplace=True)
                df_stock.to_sql('stock', con=engine, index=False, index_label='trade_code', if_exists='append')
        # logging.debug('stock_company接口返回数据：(%s)' %df_stock_company)

        # 更新覆盖原有数据(试验得将会导致数据表信息改变，不推荐)
        # df_stock_company.to_sql(name='stock_company', con=engine, index=False, index_label='trade_code', if_exists='replace')


